import sys, os
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), os.path.pardir)))
print(sys.path)

import pandas as pd
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
from AppStoreConnect import Subscription, AppProduct


if __name__ == "__main__":
    pass

    # 创建AppDB实例
    engine = create_engine('mysql+pymysql://appconnect:pobxoj-Mopsy5-bycqac@47.116.122.58:3306/app')

    # 获取从start_date到end_date的 订阅事件数据
    hearty_df = pd.read_sql('select * from subscription_event where event_date between "2024-01-01" and "2024-2-29"', engine)

    # 筛选出subscription_apple_id为'6449180970'的数据
    hearty_df = hearty_df[hearty_df['subscription_apple_id'] == AppProduct.Sub_Hearty_1_Year.value]

    activation_events = Subscription.activation_events.values()

    # 按国家统计event in activaion_events数据，quantity之和，不要index
    hearty_activation_df = hearty_df[hearty_df['event'].isin(activation_events)].groupby('country').agg({'quantity': 'sum'})

    # 按国家统计event in cancelation_events数据，quantity之和，不要index
    hearty_cancelation_df = hearty_df[hearty_df['event'].isin(Subscription.cancelation_events.values())].groupby('country').agg({'quantity': 'sum'})

    # 按国家统计event in conversion_to_standard_price_events数据，quantity之和，不要index
    hearty_conversion_to_standard_price_df = hearty_df[hearty_df['event'].isin(Subscription.conversion_to_standard_price_events.values())].groupby('country').agg({'quantity': 'sum'})  


    # 按国家合并三个DataFrame
    hearty_activation_df = hearty_activation_df.merge(hearty_cancelation_df, on='country', suffixes=('_activation', '_cancelation'))
    hearty_activation_df = hearty_activation_df.merge(hearty_conversion_to_standard_price_df, on='country')

    print(hearty_activation_df.head(10))

    # 对 activation最多的前20个国家 画图
    hearty_activation_df.nlargest(20, 'quantity_activation').plot(kind='bar', y=['quantity_activation','quantity_cancelation'])

    plt.show()
